<?php

class Default_Model_Tour extends Zend_Db_Table_Abstract
{
    protected $_db;

	public function __construct()
	{
		$this->_db = Zend_Registry::get('db');
	}

	public function mostPopular($locale)
	{
		$tour = array(
			't.id',
			't.image',
		);
		$tour_language = array(
			'tl.name',
		);
		$sql = $this->_db->select()
                        ->from('tour AS t', $tour)
                        ->join('tour_language AS tl', 'tl.tour_id = t.id', $tour_language)
                        ->join('language AS l', 'l.id = tl.language_id', null)
                        ->where('t.status = 1')
                        ->where('l.locale = ?', $locale)
                        ->order('t.id DESC')
                        ->limit(4);
    	$result = $this->_db->fetchAll($sql);
    	return $result;
	}

	public function listItem($locale)
    {
        $tour = array(
            'id',
            'image',
            'price',
        );
        $tour_language = array(
            'name',
            'schedule',
            'start_date',
            'description',
        );
        $sql = $this->_db->select()
                        ->from('tour AS t', $tour)
                        ->join('tour_language AS tl', 'tl.tour_id = t.id', $tour_language)
                        ->join('language AS l', 'l.id = tl.language_id', null)
                        ->where('t.status = 1')
                        ->where('l.locale = ?', $locale)
                        ->order('t.id DESC');
        return $sql;
    }

    public function listItemByCategory($category_id, $locale)
    {
        $tour = array(
            'id',
            'image',
            'price',
        );
        $tour_language = array(
            'name',
            'schedule',
            'start_date',
            'description',
        );
        $sql = $this->_db->select()
                        ->from('tour AS t', $tour)
                        ->join('tour_language AS tl', 'tl.tour_id = t.id', $tour_language)
                        ->join('language AS l', 'l.id = tl.language_id', null)
                        ->where('t.category_id = ?', $category_id)
                        ->where('t.status = 1')
                        ->where('l.locale = ?', $locale)
                        ->order('t.id DESC');
        return $sql;
    }

    public function listItemByLocation($location_id, $locale)
    {
        $tour = array(
            'id',
            'image',
            'price',
        );
        $tour_language = array(
            'name',
            'schedule',
            'start_date',
            'description',
        );
        $sql = $this->_db->select()
                        ->from('tour AS t', $tour)
                        ->join('tour_language AS tl', 'tl.tour_id = t.id', $tour_language)
                        ->join('language AS l', 'l.id = tl.language_id', null)
                        ->where('t.location_id = ?', $location_id)
                        ->where('t.status = 1')
                        ->where('l.locale = ?', $locale)
                        ->order('t.id DESC');
        return $sql;
    }

    public function getItem($id, $locale)
    {
        $sql = $this->_db->select()
                        ->from('tour AS t')
                        ->join('tour_language AS tl', 'tl.tour_id = t.id')
                        ->join('language AS l', 'l.id = tl.language_id', null)
                        ->where('t.id = ?', $id)
                        ->where('t.status = 1')
                        ->where('l.locale = ?', $locale);
        $result = $this->_db->fetchRow($sql);
        return $result;
    }

    public function searchItem($keyword, $locale)
    {
        $tour = array(
            'id',
            'image',
            'price',
        );
        $tour_language = array(
            'name',
            'schedule',
            'start_date',
            'description',
        );
        $sql = $this->_db->select()
                        ->from('tour AS t', $tour)
                        ->join('tour_language AS tl', 'tl.tour_id = t.id', $tour_language)
                        ->join('language AS l', 'l.id = tl.language_id', null)
                        ->where('tl.name_plaintext LIKE ?', $keyword)
                        ->where('t.status = 1')
                        ->where('l.locale = ?', $locale)
                        ->order('t.id DESC');
        return $sql;
    }
}
